home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Night Owl 6
/
Night Owl's Shareware - PDSI-006 - Night Owl Corp (1990).iso
/
025a
/
lotlib.zip
/
LOTLIB.PRG
< prev
next >
Wrap
Text File
|
1989-04-11
|
10KB
|
294 lines
** lotlib.prg
** Author: Nick Keenan, CIS Number 71641,2615
** Date: 4-11-89
** Copyright: (C) 1988,1989 Nicholas B. Keenan
******************************************************************************
*
*
* This is a set of functions written in clipper to allow you to create
* Lotus-123 type files as the output of your applications. To some extent
* it replaces the "copy type wks" command of dBase, but it allows you to go
* much further in terms of specifying the exact format.
*
* This file contains the functions, and an example program which demonstrates
* their use by copying a .dbf file to a .wk1 file.
*
* The functions are:
* lopen(filename) -- opens a file as a spreadsheet.
* Returns the dos handle on success, otherwise -1.
* lwidth(handle, column, width) -- Sets the width of column (column+1)in
* the worksheet pointed to by handle to width. Equivalent to /wcs
* in interactive lotus.
* lput(handle,value,column,row) -- puts value in cell (column+1,row+1) in
* the worksheet pointed to by handle. Makes certain assumptions about
* default formats (dates are long international, integers are comma,
* reals are fixed format, etc.) but you can adjust them.
* lclose(handle) -- write the eof string to handle and close it.
*******************************************************************************
** How lotus files are organized **
** A lotus worksheet file consists of a series of records, each of 4 or more
** bytes. The first two bytes are an integer identifying the function of this
** record. Typical functions are: 0x00 - beginning of file;0x08 - set column
** width; 0x0D - integer data; 0x0E - floating point data; 0x0F - string data;
** 0x10 - formula; etc. The next two bytes tell the length of the data portion
** of the record, if any, and the rest of the record is the data.
** I am indebted to "File Formats for Popular PC Software" by Jeff Walden,
** Wiley Press, for this information, although it is also freely available
** from Lotus.
** The only real problem with creating records of this type in clipper is the
** treatment of floating point numbers, which are required for values not
** between -32767 and 32767 as well as for non-integers. Basically what you
** need is a function that takes a number as its argument and returns an
** eight character string that is the floating-point representation of that
** number. This can be done in two ways: either in a simple C program using
** the extend system, or through a somewhat longer method in clipper. For the
** sake of example, both methods are included in this file. In addition, there
** should be a file called cfloat.obj included in this archive, which is a
** compiled version of the C function which can be linked in directly if you
** don't have a C compiler. I use the C version in my programs because it is
** faster and more elegant, although there is a certain cachet in having
** something like this written entirely in clipper.
******************************************************************************
** N O T I C E **
** This program, and the acompanying documentation and files, is released for
** the STRICT NON-COMMERCIAL use of others. If you intend to use any part of
** it for any commercial purpose, you MUST obtain the permission of the
** copyright holder. Portions of this code are currently being used in a
** commercial software product; if your product is not a competitor of ours,
** we will probably let you use it in your product. In addition, we would
** like to hear comments, questions and additions from anyone who uses
** these functions.
** Our address is: PO Box 2133, Hoboken, NJ, 07030. (201)963-1000.
** H E L P W A N T E D **
** Could you write a program like this? Could you write a better one? Are
** you interested in working in Charlottesville, VA ? Our company , a small
** but aggressive publishing/research/consulting partnership, specializing in
** financial intitutions, is looking for several experienced clipper
** programmers to work as full-time employees starting in July of 1989.
** Job duties include producing and developing database and print
** publications, supervising a local area network, user support, etc. We are
** big in desktop publishing and PC to fax broadcasting. We are a young
** company (2 years old) where your capabilities will be tested and rewarded.
** We have no bureaucracy and dress casually. If you are interested in
** getting in on the bottom floor, and working in one of the prettiest cities
** in America (Charlottesville, not Hoboken), contact us in confidence
** at the address above.
** enough propaganda. Here's the sample program.
clear
fname=space(8)
@ 10,10 say "Input name of file to translate:" get fname
read
if lastkey() =27
quit
endif
if .not. file(fname+'.dbf')
?'File not found'
quit
endif
if file(fname+'.wk1')
wait 'File '+fname+'.wk1 already exists. Overwrite Y/N ?' to ch
if upper(ch) != 'Y'
quit
endif
endif
use &fname
handle = lopen(fname)
for x=1 to fcount()
fld=field(x)
do case
case type(fld) ='D'
lwidth(handle,x-1,9)
case type(fld) ='L'
lwidth(handle,x-1,2)
case type(fld) ='C'
lwidth(handle,x-1,len( &fld) )
case type(fld) ='N'
if getdecs(&fld) = 0
lwidth(handle,x-1,int((len(str(&fld))+2)*4/3)) && make wide enough for commas
else
lwidth(handle,x-1,len(str(&fld)))
endif
endcase
lput(handle,fld,x-1,0) && put in labels
next
do while .not. eof() .and. inkey() <>27
for x=1 to fcount()
fld=field(x)
lput(handle, &fld,x-1,recno())
next
@ 24,3 say str(recno())+" Records copied"
skip
enddo
lclose(handle)
return
******************************************************************************
*** lotus interface funtions
****************************************************
** lopen(file)
** open a file as a lotus worksheet
** returns dos handle, -1 on error
FUNCTION lopen
param fname
private mname,handle
if .not. "." $ fname
mname=trim(fname)+".wk1"
else
mname=fname
endif
handle =fcreate(mname,0)
** put in bof string
if handle <>-1
fwrite(handle,chr(0)+chr(0)+chr(2)+chr(0)+chr(6)+chr(4))
endif
return handle
************************************************************************
*lwidth(handle,col,width)
* set width of specified column to width in file handle
FUNCTION lwidth
param handle, col, width
return fwrite(handle,chr(8)+chr(0)+chr(3)+chr(0)+lotwrd(col)+chr(width))
**************************************************************************
*lput(handle,val,col,row)
* put a value into cell col, row
FUNCTION lput
param handle,val,col,row
private mstr, decs
do case
case type("val")="C"
mstr=chr(15)+chr(0)+lotwrd(len(val)+7)+chr(255)+lotwrd(col)+lotwrd(row)+"'"+val+chr(0)
case type("val")="D"
mstr=chr(14)+chr(0)+chr(13)+chr(0)+chr(249)+lotwrd(col)+lotwrd(row)+cfloat(val-ctod('01/01/00')+2)
case type("val")="N"
decs=getdecs(val)
if decs <>0
mstr=chr(14)+chr(0)+chr(13)+chr(0)+chr(128+decs)+lotwrd(col)+lotwrd(row)+cfloat(val)
else
mstr=chr(14)+chr(0)+chr(13)+chr(0)+chr(192)+lotwrd(col)+lotwrd(row)+cfloat(val)
endif
case type("val")="L"
mstr=chr(15)+chr(0)+chr(8)+chr(0)+chr(255)+lotwrd(col)+lotwrd(row)+"'"+if(val,"Y","N")+chr(0)
endcase
return fwrite(handle,mstr)
********************************************************************************
****lclose(handle)
** close a lotus file
FUNCTION lclose
param handle
fwrite(handle,chr(01)+chr(0)+chr(0)+chr(0))
fclose(handle)
return .t.
************************************************************************************
FUNCTION lotwrd
** returns the lotus format of a number (lsb first)
param mvalue
return chr(mvalue%256)+chr(int(mvalue/256))
FUNCTION getdecs
** get number of decimal places in a number
param mnum
private smnum, at
smnum=str(mnum)
at=at('.',smnum)
if at=0
return 0
else
return len(smnum)-at
endif
*******************************************************************************
** return the string form of a double
** offered as an alternative to the C version
function cfloat
param var
private retval, mantissa, sign ,x
if var=0
return replicate(chr(0),8)
endif
if var <0
sign =128
else
sign =0
endif
mantissa=1075
var=abs(var)
do while var < 4503599627370496
var=var*2
mantissa=mantissa-1
enddo
do while var > 2*4503599627370496-1
var=var/2
mantissa=mantissa+1
enddo
var=int(var)
retval=''
for x= 1 to 6
retval=retval+chr(var%256)
var=int(var/256)
next
var=var-16
retval=retval+chr(var+16*(mantissa%16))
retval=retval+chr(sign+int(mantissa/16))
return retval
********************************************************************
** The C version is much simpler:
** #include "extend.h"
**
** CLIPPER cfloat()
** {
** double flvar;
** char *x;
** flvar=_parnd(1);
** x=(char *)&flvar;
** _retclen(x,8);
** }
*************************************************************************
**[EOF]